Third-party Database Connection

You can either collect data from edge devices through X-Collector, or connect a third-party database to the supOS platform and call data through template services.

Connecting Database

1. Log in to supOS, and then click to enter the design center.
2. Select Global Configuration > Data Source Management on the left side in design center, and then click New.
   

info

Multiple data sources are available. MySQL is used as an example in this chapter.

3. Select mySQL, and then enter the basic information of the database.

4. Click Connection Test to check whether the database is correctly connected.
5. Click Confirm.

Creating Service

Databased added under Global Configuration cannot be directly used. An object template is needed to introduce data from the connected third-party databases.

1. In the design center of supOS platform, select Object Model Management > Object Template.
2. Select Form Template, and then click .
   • Entity Template is often used to describe data collected through X-Collector.
   • Form Template is frequently used to introduce data from third-party databases.
3. Enter information of the template, and then click Save.
   tip

   You can leave optional parameters as default.

4. Click Services tab, and then click New under My Services.
   • Enter basic information of the service. Make sure the alias is unique.
   • Under Information Input, you can add fields as input parameters of the service. You can also leave it empty and then define input parameters in the script.
   • Set Type to JSON under Information Output.
   • Enter in Return Format for now.

5. Click Script tab, and then enter the script.
   • No input parameters are added under Service Information.

info

Value of sourceId and sql variables in the code below are for reference only.

var JsonObject = Java.type("io.vertx.core.json.JsonObject")
var httpService = services["HttpClientService"];
var ArrayList = Java.type("java.util.ArrayList");

var sourceId = "<em>database id</em>";
var sql = "<em>sql command</em>";

var param = {
    id: sourceId,
    sql: sql,
    enableTotal: true,
    pageSize: 10000
}

res = httpService.post("http://compose-manage:8080/api/compose/manage/datatable/exec", JSON.stringify(param), 10000);
list = JSON.parse(res).body.data.dataSource;

result = {
    list: new ArrayList(list)
}

• Added input parameters under Service Information.
  info

  You need to manually enter information of the database, including sourceId, sql command and page size.

var JsonObject = Java.type("io.vertx.core.json.JsonObject")
var httpService = services["HttpClientService"];
if(!pageSize || pageSize <0) {
    pageSize = 2000;
}
var param = {
    id:sourceId,
    sql:sql,
    enableTotal: true,
    pageSize:pageSize
}
var header = {
    "X-Tenant-Id":"dt"
}
var res = httpService.post("http://compose-manage:8080/api/compose/manage/datatable/exec",JSON.stringify(param),header,10000)
res

6. Click OK.

Obtaining Database ID

To get data from the connected database, its ID is necessary.

1. In the design center, select Global Configuration > Data Source Management to enter the database connection page.
2. Press F12 to bring out the console, and then press F5 to refresh the page.
3. Under Network on the console, click the query tab.
   All information of the listed databases are displayed under infos in order.
4. Copy the id of corresponding database and then paste it to the service.